style24.doc/산출물/3.설계/03.인터페이스/더존/[dbo].[USP_ORD_SettleConfirmByReName_Q_DOUZONE].sql

/*    
  
----------------------------------------------------------------------------------    
  
◑ SP Name    : dbo.USP_ORD_SettleConfirmByReName_Q_DOUZONE 0, '2018-01-01', '2018-01-31', '정산완료', ''    
  
◑ Description   : 공급사별 정산예정목록을 조회한다.    
  
◑ Called by   :     
  
◑ Input Parameters  :     
  
◑ Output Parameters :     
  
◑ Exec     :     
  
◑ Change History    
  
----------------------------------------------------------------------------------    
  
Date  Author  Description    
  
----------------------------------------------------------------------------------    
  
2018-03-06 성근영  최초 생성  
2019-06-11 이선미 정산액 수정 및튜닝 (임시테이블 제거, 그룹바이 중복 제거)  
2019-07-02 이선미 정산액 마이너스 금액도 포함   
  
----------------------------------------------------------------------------------    
  
*/    
  
CREATE PROCEDURE [dbo].[USP_ORD_SettleConfirmByReName_Q_DOUZONE]   
 @ProviderNo   INT,    
 @DateFrom   DATETIME,    
 @DateTo    DATETIME,    
 @SettleStatusCd  NVARCHAR(20),  
 @SettleDate   CHAR(10)    
AS    
  
BEGIN     
 SET NOCOUNT ON;      
  
 DECLARE @StrDateFrom Nvarchar(10), @StrDateTo Nvarchar(10)    
 SET @StrDateFrom = Convert(Nvarchar(10), @DateFrom, 121)    
 SET @StrDateTo  = Convert(Nvarchar(10), DateAdd(day, 1, @DateTo), 121)   
    
  ;WITH CTE_Settle(BizNumber,settleDate, CouponPrice, CouponAllotment, OrderSettleAmount, MarginSettleAmount, Amount)  
 AS(  
  SELECT V.BizNumber  
  , V.SettleDate  
  , SUM(V.CouponPrice) AS CouponPrice  
  , SUM(V.CouponAllotment) AS CouponAllotment  
  , SUM(V.OrderSettleAmount) AS OrderSettleAmount  
  , SUM(V.MarginSettleAmount) AS MarginSettleAmount  
  , SUM(V.OrderSettleAmount + V.CouponPrice - V.MarginSettleAmount-V.CouponAllotment) As Amount   
  --, CAST(ROUND(SUM(V.MarginSettleAmount / 1.1),4) AS float)  As SupAmount   
  --, CAST(ROUND(SUM(V.MarginSettleAmount) - SUM(V.MarginSettleAmount / 1.1),4) AS float) As Tax  
   FROM  
  
   (   SELECT  VD.BizNumber  
   , V.SettleDate  
   , V.SettleStatusCd    
   , V.VendorNo  
      , V.CouponPrice  
   , V.CouponAllotment   
   , V.OrderSettleAmount    
   ,V.MarginSettleAmount  
    FROM dbo.VW_ORD_Settlement AS V WITH (NOLOCK)   
   INNER JOIN dbo.TB_VEN_Provider AS P WITH (NOLOCK) ON V.ProviderNo = P.ProviderNo    
   INNER JOIN dbo.TB_VEN_Vendor AS VD WITH (NOLOCK) ON P.VendorNo = VD.VendorNo    
    WHERE V.DateSales >= @StrDateFrom AND V.DateSales < @StrDateTo    
    AND CASE WHEN @SettleStatusCd = '' THEN '' ELSE SettleStatusCd END = @SettleStatusCd  
    AND CASE WHEN @SettleDate = '' THEN '' ELSE SettleDate END = @SettleDate   
   ) V  
   GROUP BY V.VendorNo, V.BizNumber, V.SettleDate, V.SettleStatusCd    
   )  
  
  
  
   --더존계산식   
  SELECT BizNumber, SettleDate  
  , CAST(ROUND((OrderSettleAmount) * -1 ,0) AS numeric(19, 4)) AS '상품매출액'  
     , CAST(ROUND(MarginSettleAmount -( CouponPrice- CouponAllotment), 0) - ROUND((MarginSettleAmount -( CouponPrice- CouponAllotment)) / 1.1, 0) AS numeric(19,4)) AS '예수부가세'  
     , CAST(ROUND(((MarginSettleAmount -( CouponPrice- CouponAllotment)) / 1.1), 0) AS numeric(19, 4)) AS '수수료매출'  
     , CAST((Amount) AS numeric(19, 4)) AS '미지급금'  
  FROM CTE_Settle;  
   --WHERE OrderSettleAmount > 0  
    
/*  
 DECLARE @TargetListTable Table (    
  
 StrDateFrom Nvarchar(10)  
  
  , StrDateTo Nvarchar(10)  
  
  , BizNumber Nvarchar(50)    
  
  , SettleDate Nvarchar(10)  
  
  , SettleStatusCd Nvarchar(20)    
  
  
  
  , CouponPrice Money  
  
  , CouponAllotment Money  
  
  , OrderSettleAmount Money  
  
  , MarginSettleAmount Money  
  
  , Amount Money  
  
  , SupAmount Money    
  
  , Tax Money  
  
 )    
  
    
  
 INSERT INTO @TargetListTable    
  
  SELECT @StrDateFrom, @StrDateTo, V.BizNumber, V.SettleDate, V.SettleStatusCd   
  
 , SUM(V.CouponPrice) AS CouponPrice  
  
 , SUM(V.CouponAllotment) AS CouponAllotment  
  
 , SUM(V.OrderSettleAmount) AS OrderSettleAmount  
  
 , SUM(V.MarginSettleAmount) AS MarginSettleAmount  
  
    , SUM(V.OrderSettleAmount + V.CouponPrice - V.MarginSettleAmount) As Amount   
  
 , SUM(V.MarginSettleAmount / 1.1) As SupAmount   
  
    , SUM(V.MarginSettleAmount) - SUM(V.MarginSettleAmount / 1.1) As Tax  
  
  FROM  
  
  (  
  
    SELECT V.VendorNo, V.VendorName, V.ProviderNo, V.ProviderName, V.DeliveryUnitNo, V.DeliveryUnitName, VD.Representative, VD.BizNumber, P.BankAccountInfo, V.SettleDate, V.SettleStatusCd    
  
  , SUM(V.CouponPrice) AS CouponPrice, SUM(V.CouponAllotment) AS CouponAllotment, SUM(V.OrderSettleAmount) AS OrderSettleAmount    
  
  , ROUND(SUM(V.MarginSettleAmount),0) AS MarginSettleAmount  
  
  , SUM((V.OrderSettleAmount+V.CouponPrice) - V.MarginSettleAmount) As Amount    
  
  , ROUND(ROUND(SUM(V.MarginSettleAmount),0) / 1.1, 0) As SupAmount  
  
  , ROUND(SUM(V.CouponPrice+V.OrderSettleAmount),0) as StandardTotalPrice    
  
  , ROUND(SUM(V.MarginSettleAmount),0) -  ROUND(ROUND(SUM(V.MarginSettleAmount),0) / 1.1, 0) As Tax    
  
  , SUM(V.OrderSurTax) AS OrderSurTax, ROUND(SUM(V.MarginSurTax),0) AS MarginSurTax    
  
   FROM dbo.VW_ORD_Settlement AS V WITH (NOLOCK)   
  
  INNER JOIN dbo.TB_VEN_Provider AS P WITH (NOLOCK) ON V.ProviderNo = P.ProviderNo    
  
  INNER JOIN dbo.TB_VEN_Vendor AS VD WITH (NOLOCK) ON P.VendorNo = VD.VendorNo    
  
   WHERE V.DateSales >= @StrDateFrom AND V.DateSales < @StrDateTo    
  
   GROUP BY V.VendorNo, V.VendorName, V.ProviderNo, V.ProviderName, V.DeliveryUnitNo, V.DeliveryUnitName, VD.Representative, VD.BizNumber, P.BankAccountInfo, V.SettleDate, V.SettleStatusCd    
  
  ) V  
  
  --FROM dbo.VW_ORD_Settlement AS V WITH (NOLOCK)   
  
  --  INNER JOIN dbo.TB_VEN_Vendor AS VD WITH (NOLOCK) ON V.VendorNo = VD.VendorNo    
  
  --WHERE V.DateSales >= @StrDateFrom   
  
  --AND   V.DateSales < @StrDateTo  
  
  GROUP BY V.VendorNo, V.BizNumber, V.SettleDate, V.SettleStatusCd    
  
    
  
 If @SettleStatusCd <> ''    
  
  DELETE FROM @TargetListTable WHERE SettleStatusCd <> @SettleStatusCd    
  
    
  
 If @SettleDate <> ''    
  
  DELETE FROM @TargetListTable WHERE SettleDate <> @SettleDate    
  
      
  
 SELECT BizNumber, SettleDate, CouponPrice, CouponAllotment, OrderSettleAmount  
  
      , MarginSettleAmount, Amount, SupAmount, Tax   
  
 FROM @TargetListTable    
  
   
 */  
   
  
 SET NOCOUNT OFF;    
  
END